package com.yifi.common;

import java.math.BigDecimal;
import java.util.Date;
import java.util.List;

//import net.sf.json.JSONObject;

import com.alibaba.fastjson.JSONObject;
import com.jfinal.plugin.activerecord.Db;
import com.jfinal.plugin.activerecord.Record;

public class DBHelper {

	public static Date GetDateNow() {

		/*
		 * Date newdate = new
		 * SimpleDateFormat("yyyy-MM-dd HH:mm:ss").parse("2015-11-24 02:20:22",new
		 * ParsePosition(0)); Date newdate = new
		 * SimpleDateFormat("yyyy-MM-dd").parse("2015-11-24",new ParsePosition(0));
		 * java.sql.Time time = new java.sql.Time(newdate.getTime());
		 */

		return Db.queryDate("select now() from dual");// 只能得到日期
//		return new java.sql.Time(new Date().getTime());
//		return new java.sql.Time(System.currentTimeMillis());
	}

	public static List<Record> GetList(String sql) {
		return Db.find(sql);
	}

	public static String GetString(String sql) {
		String value = Db.queryStr(sql);
		value = (value == null) ? "" : value;
		return value;
	}

	public static String GetString(Record record, String columnName) {
		String value = record.getStr(columnName);
		value = (value == null) ? "" : value;
		return value;
	}

	public static String GetString(List<Record> listR, int rindex, String columnName) {
		if (listR == null) {
			return "";
		}
		return GetString(listR.get(rindex), columnName);
	}

	public static String GetString(JSONObject jsonJ, String key) {
		String value = "";
		if (jsonJ.get(key) != null) {
			value = jsonJ.getString(key);
		}
		return value;
	}

	public static Long GetLong(String sql) {
		Long dvalue = Db.queryLong(sql);
		Long value = (dvalue == null) ? 0 : dvalue.longValue();
		return value;
	}

	public static Integer GetInt(String sql) {
		BigDecimal dvalue = Db.queryBigDecimal(sql);
		Integer value = (dvalue == null) ? 0 : dvalue.intValue();
		return value;
	}

	public static Integer GetInt(String sql, Object... paras) {
		BigDecimal dvalue = Db.queryBigDecimal(sql, paras);
		Integer value = (dvalue == null) ? 0 : dvalue.intValue();
		return value;
	}

	public static Integer GetInt(Record record, String columnName) {
		BigDecimal dvalue = record.getBigDecimal(columnName);
		Integer value = (dvalue == null) ? 0 : dvalue.intValue();
		return value;
	}

	public static Integer GetInt(List<Record> listR, int rindex, String columnName) {
		return GetInt(listR.get(rindex), columnName);
	}

	public static Double GetDouble(String sql) {
		BigDecimal dvalue = Db.queryBigDecimal(sql);
		Double value = (dvalue == null) ? 0 : dvalue.doubleValue();
		return value;
	}

	public static Double GetDouble(Record record, String columnName) {
		BigDecimal dvalue = record.getBigDecimal(columnName);
		Double value = (dvalue == null) ? 0 : dvalue.doubleValue();
		return value;
	}

	public static Double GetDouble(List<Record> listR, int rindex, String columnName) {
		return GetDouble(listR.get(rindex), columnName);
	}

	public static void Set(List<Record> listR, int rindex, String column, Object value) {
		listR.get(rindex).set(column, value);
	}

	public static boolean ExecNonQuery(String tableName, String primaryKey, Record record, boolean isedit) {
		boolean tag = false;
		try {
			if (isedit) {
				tag = Db.update(tableName, primaryKey, record);
			} else {
				tag = Db.save(tableName, primaryKey, record);
			}
		} catch (Exception e) {
			System.out.println(e.toString());
			return false;
		}
		return tag;
	}

	public static int FindIndex(List<Record> rs, String columnName, String cvalue) {
		int rindex = -1;
		for (int i = 0; i < rs.size(); i++) {
			if (GetString(rs, i, columnName).equals(cvalue)) {
				rindex = i;
				break;
			}
		}
		return rindex;
	}

	// 获取oracle表字段及注释
	public static List<Record> GetTableCol(String tableName) {
		// 注释默认取系统注释，存在datawindow配置表则取pbc
		// oracle注释
		String sql_comment = ",nvl((select comments from user_col_comments where table_name=t1.table_name and column_name=t1.column_name),'') as cdesc";

		// datawindow注释
		if (GetInt("select count(column_name) from user_tab_columns where table_name='PBCATCOL'") > 0) {
			sql_comment = ",nvl((select pbc_cmnt from pbcatcol where pbc_tnam=t1.table_name and pbc_cnam=t1.column_name),'') as cdesc";
		}

		// 表名为大写
		tableName = tableName.toUpperCase();
		String sql = "select table_name as tname,column_name as cname" + sql_comment
				+ " from user_tab_columns t1 where table_name='" + tableName + "'";
		return GetList(sql);
	}
}
